1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 namespace WarehouseManagementSystem
12 {
13 public partial class frmStockRecord : Form
14 {
15 SqlDataReader rdr = null;
16 SqlConnection con = null;
17 SqlCommand cmd = null;
18 ConnectionString cs = new ConnectionString();
19 public frmStockRecord()
20 {
21 InitializeComponent();
22 }
23 public void GetData()
24 {
25 try
26 {
27 con = new SqlConnection(cs.DBConn);
28 con.Open();
29 String sql = "SELECT RTRIM(StockID),RTRIM(StockDate),RTRIM(Product.ProductID),RTRIM(ProductName),RTRIM(Features),RTRIM(Supplier.SupplierID),RTRIM(SupplierName),RTRIM(Quantity),RTRIM(ExpiryDate),RTRIM(ExpiryDate) from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID order by ProductName";
30 cmd = new SqlCommand(sql, con);
31 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
32 dataGridView1.Rows.Clear();
33 while (rdr.Read() == true)
34 {
35 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5],rdr[6],rdr[7],rdr[8]);
36 }
37 con.Close();
38 }
39 catch (Exception ex)
40 {
41 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
42 }
43 }
44 private void frmStockRecord_Load(object sender, EventArgs e)
45 {
46 GetData();
47 }
48
49 private void txtProductname_TextChanged(object sender, EventArgs e)
50 {
51 try
52 {
53 con = new SqlConnection(cs.DBConn);
54 con.Open();
55 String sql = "SELECT RTRIM(StockID),RTRIM(StockDate),RTRIM(Product.ProductID),RTRIM(ProductName),RTRIM(Features),RTRIM(Supplier.SupplierID),RTRIM(SupplierName),RTRIM(Quantity),RTRIM(ExpiryDate) from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID and productname like '" + txtProductname.Text + "%' order by ProductName";
56 cmd = new SqlCommand(sql, con);
57 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
58 dataGridView1.Rows.Clear();
59 while (rdr.Read() == true)
60 {
61 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6],rdr[7],rdr[8]);
62 }
63 con.Close();
64 }
65 catch (Exception ex)
66 {
67 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
68 }
69 }
70
71 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
72 {
73 string strRowNumber = (e.RowIndex + 1).ToString();
74 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
75 if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
76 {
77 dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
78 }
79 Brush b = SystemBrushes.ControlText;
80 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
81
82 }
83
84
85 private void Button4_Click(object sender, EventArgs e)
86 {
87 int rowsTotal = 0;
88 int colsTotal = 0;
89 int I = 0;
90 int j = 0;
91 int iC = 0;
92 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
93 Excel.Application xlApp = new Excel.Application();
94
95 try
96 {
97 Excel.Workbook excelBook = xlApp.Workbooks.Add();
98 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
99 xlApp.Visible = true;
100
101 rowsTotal = dataGridView1.RowCount;
102 colsTotal = dataGridView1.Columns.Count - 1;
103 var _with1 = excelWorksheet;
104 _with1.Cells.Select();
105 _with1.Cells.Delete();
106 for (iC = 0; iC <= colsTotal; iC++)
107 {
108 _with1.Cells[1, iC + 1].Value = dataGridView1.Columns[iC].HeaderText;
109 }
110 for (I = 0; I <= rowsTotal - 1; I++)
111 {
112 for (j = 0; j <= colsTotal; j++)
113 {
114 _with1.Cells[I + 2, j + 1].value = dataGridView1.Rows[I].Cells[j].Value;
115 }
116 }
117 _with1.Rows["1:1"].Font.FontStyle = "Bold";
118 _with1.Rows["1:1"].Font.Size = 12;
119
120 _with1.Cells.Columns.AutoFit();
121 _with1.Cells.Select();
122 _with1.Cells.EntireColumn.AutoFit();
123 _with1.Cells[1, 1].Select();
124 }
125 catch (Exception ex)
126 {
127 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
128 }
129 finally
130 {
131 //RELEASE ALLOACTED RESOURCES
132 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
133 xlApp = null;
134 }
135 }
136
137 private void button2_Click(object sender, EventArgs e)
138 {
139 txtProductname.Text = "";
140 dtpStockDateFrom.Text = System.DateTime.Today.ToString();
141 dtpStockDateTo.Text = System.DateTime.Today.ToString();
142 GetData();
143 }
144
145 private void button1_Click(object sender, EventArgs e)
146 {
147 try
148 {
149 con = new SqlConnection(cs.DBConn);
150 con.Open();
151 String sql = "SELECT RTRIM(StockID),RTRIM(StockDate),RTRIM(Product.ProductID),RTRIM(ProductName),RTRIM(Features),RTRIM(Supplier.SupplierID),RTRIM(SupplierName),RTRIM(Quantity),RTRIM(ExpiryDate) from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID and StockDate between @d1 and @d2 order by ProductName";
152 cmd = new SqlCommand(sql, con);
153 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "StockDate").Value = dtpStockDateFrom.Value.Date;
154 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "StockDate").Value = dtpStockDateTo.Value.Date;
155 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
156 dataGridView1.Rows.Clear();
157 while (rdr.Read() == true)
158 {
159 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7],rdr[8]);
160 }
161 con.Close();
162 }
163 catch (Exception ex)
164 {
165 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
166 }
167 }
168
169 private void button5_Click(object sender, EventArgs e)
170 {
171 try
172 {
173 if (txtProductname.Text == "")
174 {
175 MessageBox.Show("Please enter product name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
176 txtProductname.Focus();
177 return;
178 }
179 Cursor = Cursors.WaitCursor;
180 timer1.Enabled = true;
181 rptStock rpt = new rptStock();
182 //The report you created.
183 cmd = new SqlCommand();
184 SqlDataAdapter myDA = new SqlDataAdapter();
185 POS_DBDataSet myDS = new POS_DBDataSet();
186 //The DataSet you created.
187 con = new SqlConnection(cs.DBConn);
188 cmd.Connection = con;
189 cmd.CommandText = "SELECT * from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID and ProductName like '" + txtProductname.Text + "%' order by StockDate";
190 cmd.CommandType = CommandType.Text;
191 myDA.SelectCommand = cmd;
192 myDA.Fill(myDS, "Stock");
193 myDA.Fill(myDS, "Product");
194 myDA.Fill(myDS, "Supplier");
195 rpt.SetDataSource(myDS);
196 frmStockReport frm = new frmStockReport();
197 frm.crystalReportViewer1.ReportSource = rpt;
198 frm.Visible = true;
199 }
200 catch (Exception ex)
201 {
202 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
203 }
204 }
205
206 private void timer1_Tick(object sender, EventArgs e)
207 {
208 Cursor = Cursors.Default;
209 timer1.Enabled = false;
210 }
211
212 private void button3_Click(object sender, EventArgs e)
213 {
214 try{
215 Cursor = Cursors.WaitCursor;
216 timer1.Enabled = true;
217 rptStock rpt = new rptStock();
218 //The report you created.
219 cmd = new SqlCommand();
220 SqlDataAdapter myDA = new SqlDataAdapter();
221 POS_DBDataSet myDS = new POS_DBDataSet();
222 //The DataSet you created.
223 con = new SqlConnection(cs.DBConn);
224 cmd.Connection = con;
225 cmd.CommandText = "SELECT * from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID and StockDate Between @d1 and @d2 order by StockDate";
226 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "StockDate").Value = dtpStockDateFrom.Value.Date;
227 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "StockDate").Value = dtpStockDateTo.Value.Date;
228 cmd.CommandType = CommandType.Text;
229 myDA.SelectCommand = cmd;
230 myDA.Fill(myDS, "Stock");
231 myDA.Fill(myDS, "Product");
232 myDA.Fill(myDS, "Supplier");
233 rpt.SetDataSource(myDS);
234 frmStockReport frm = new frmStockReport();
235 frm.crystalReportViewer1.ReportSource = rpt;
236 frm.Visible = true;
237 }
238 catch (Exception ex)
239 {
240 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
241 }
242 }
243 }
244 }
